/*FIGURE 1: Changes in Skill Requirements by Year (Aggregate)*/

use bgt_employer_firm_07_to_14.dta, clear

gen educ=edu
replace educ=maxedu if educ==.

gen exper=exp
replace exper=maxexp if exp==.

drop edu maxedu exp maxexp

drop if inlist(statefip, 11, 72, 66)

gen expreq=exper!=.
gen edureq=(educ>=12 &!missing(educ))
gen exp2=(exper>=2 &!missing(exper))
gen expgt2=(exper>2 &!missing(exper))
gen exp5=(exper>=5 &!missing(exper))
gen exp4=(exper>=5 &!missing(exper))
gen edubd=(educ==16  &!missing(educ))
gen edugd=(educ>=18 &!missing(educ))
gen edugths=(educ>12 &!missing(educ))
gen edubdgd=(educ>=16 &!missing(educ))
gen jobs=1

collapse (sum) jobs (mean) expreq edureq exp2  expgt2  exp5  edubd  edugd  edugths edubdgd, by(year) 
save ./data/aggregate_skill_by_year.dta, replace

collapse (sum) jobs (mean) expreq edureq exp2  expgt2  exp5  edubd  edugd  edugths edubdgd, by(month year) 

gen date=ym(year, month)
format date %tm

merge m:1 year month using ur_us_monthly, keep(match) assert(2 3)
rename usra_emplr ur

use aggregate_skill_by_year.dta, clear
gen ur=4.6 if year==2007
replace ur=9.6 if year==2010
replace ur=8.9 if year==2011
replace ur=8.1 if year==2012
replace ur=7.4 if year==2013
replace ur=6.1 if year==2014
tsset year

foreach var of varlist expreq edureq exp2  expgt2  exp5  edubd  edugd  edugths edubdgd {
gen shr`var'=(`var'/jobs)*100
}

label variable ur "Annual Unemployment Rate"
label variable exp4 "Share of Postings Requiring >=4 Years Experience"
label variable edubdgd "Share of Posting Requiring Bachelor's Deg. or Greater"

#delimit ;
twoway scatter edubdgd year, connect(l) msymbol(circle) msize(vsmall) yaxis(2) legend(label("1" "Education Requirements") size(small))  ||  
scatter ur year, connect(l) msymbol(circle) msize(vsmall) ytitle(,axis(1) size(small) orientation(rvertical))  legend( label("2" "UR") size(small))
ytitle(,axis(2) size(small) height(8)) plotregion(fcolor(white)) graphregion(fcolor(white)) legend(region(lcolor(white))) ;
graph export average_skill_a.eps, replace;
;

#delimit ;
twoway scatter exp4 year, connect(l) msymbol(circle) msize(vsmall) yaxis(2) legend(label("1" "Experience Requirements") size(small))  ||  
scatter ur year, connect(l) msymbol(circle) msize(vsmall) ytitle(,axis(1) size(small) orientation(rvertical))  legend( label("2" "UR") size(small))
ytitle(,axis(2) size(small) height(8)) plotregion(fcolor(white)) graphregion(fcolor(white)) legend(region(lcolor(white))) ;
graph export average_skill_b.eps, replace ;
;





